If exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[GetSupervisorsForImpersonation]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[GetSupervisorsForImpersonation]
GO

/******************************************************************          
* Name:   [GetSupervisorsForImpersonation]          
* Purpose:  Retrieves COI Users based on Accessrights(Supervisor at present)  
* Note: This stored procedure is mainly intended to bring back supervisors of the system as follows      
  1.If only need users with access rights(supervisor) down the hierarchy(All levels).      
  2.If only need users with access rights(supervisor) directly under a level(unlike all levels).      
  3.If need users with access rights(supervisor) all over the system.
  4.If only need supervisors, who have the logged in Person as delegate(s).             
*          
* PARAMETERS          
* Name          Description               
* -------------      -------------------------------------------          
* @admPersonId    Admin person Id (REQUIRED)            
* @firstName      FirstName of the user.          
* @lastName       LastName for the user.          
* @userName       UserName for the user.      
* @isSystemWide   determines whether or not to get all the supervisors(whole of system irrespective of hierarchy)matching the criteria      
* @isRecursive    determines whether to bring back the entire descendants or the direct descendants      
* @HierarchyTypeId. Users Hierarchy Type.     
*********************************************************************/          
CREATE PROCEDURE [dbo].[GetSupervisorsForImpersonation]           
(              
@admPersonId INT,              
@firstName  VARCHAR(64)=null,              
@lastName VARCHAR(64)=null,              
@userName VARCHAR(36)=null,      
@isSystemWide BIT=1,              
@isRecursive BIT= 1,
@HierarchyTypeId INT = 0   
)              
AS BEGIN              
  
SET NOCOUNT ON              
  
	IF(@firstName IS NULL)              
		SET @firstName='%'              
	ELSE             
		SET @firstName=@firstName+'%'               
	  
	IF(@lastName IS NULL)              
		SET @lastName='%'              
	ELSE               
		SET @lastName=@lastName+'%'               
	  
	IF(@userName IS NULL)              
		SET @userName='%'              
	ELSE              
		SET @userName=@userName+'%'               
 
	DECLARE @isDelegate BIT
	SET @isDelegate = (SELECT (CASE COUNT(SupervisorId)
						WHEN 0 THEN 0
						ELSE  1
						END) AS IsDelegate FROM COI_Delegate_Map WHERE DelegateId = @admPersonId)

	DECLARE @loggedInAccessRights INT
	SET @loggedInAccessRights =(SELECT fac.[ModuleRoleBitmask] FROM AdmPerson adp      
								LEFT JOIN FWKApplicationUser fac ON adp.[fwkDomainUserId]=fac.[FwkDomainUserId]
								WHERE adp.Id = @admPersonId)

 -- If the logged in user is a delegate, but not a super user
	IF (@isDelegate = 1 And ((@loggedInAccessRights & 2048) = 0))
	BEGIN
		SELECT DISTINCT adp.[Id] AS [PersonId],adp.[LastName]+ ', '+adp.[FirstName] AS [Name]       
		FROM COI_Delegate_Map cdm 
			INNER JOIN AdmPerson adp ON cdm.SupervisorId = adp.Id		      
			INNER JOIN FWKApplicationUser fac ON adp.[fwkDomainUserId]=fac.[FwkDomainUserId]           
			INNER JOIN FwkDomainUser fdu ON adp.[fwkDomainUserId]=fdu.[Id]
			INNER JOIN COI_Hierarchy_Map chm ON adp.Id = chm.ChildId-- OR adp.Id = chm.ParentId)
		WHERE
			cdm.DelegateId = @admPersonId
			AND
			((fac.[ModuleRoleBitmask] & 512)>0)  
			AND          
			ISNULL(adp.[firstName], '*') LIKE @firstName              
			AND              
			ISNULL(adp.[lastName], '*') LIKE @lastName              
			AND              
			ISNULL(fdu.[userName], '*') LIKE @userName    
			AND
			chm.HierarchyTypeId = CASE @HierarchyTypeId
									WHEN 0 THEN chm.HierarchyTypeId
									ELSE @HierarchyTypeId
								  END
		ORDER BY [Name]--adp.[LastName]
		RETURN
	END
	IF (@isSystemWide =1) -- Get the users (with supervisor access)all over the system irrsepective of levels      
	BEGIN        
		SELECT DISTINCT adp.[Id] AS [PersonId],adp.[LastName]+ ', '+adp.[FirstName] AS [Name]       
		FROM AdmPerson adp      
			INNER JOIN COI_Hierarchy_Map chm ON adp.Id = chm.ChildId-- OR adp.Id = chm.ParentId)
			LEFT JOIN FWKApplicationUser fac ON adp.[fwkDomainUserId]=fac.[FwkDomainUserId]           
			LEFT JOIN FwkDomainUser fdu ON adp.[fwkDomainUserId]=fdu.[Id]      
		WHERE       
			((fac.[ModuleRoleBitmask] & 512)>0)      
			AND          
			ISNULL(adp.[firstName], '*') LIKE @firstName              
			AND              
			ISNULL(adp.[lastName], '*') LIKE @lastName              
			AND              
			ISNULL(fdu.[userName], '*') LIKE @userName     
			AND
			chm.HierarchyTypeId = CASE @HierarchyTypeId
									WHEN 0 THEN chm.HierarchyTypeId
									ELSE @HierarchyTypeId
								  END
			ORDER BY [Name]--adp.[LastName]    
	END      
	ELSE IF (@isSystemWide=0)       
	BEGIN      
		IF (@isRecursive=1)      
		BEGIN      
			SELECT des.[ChildId],adp.[LastName]+ ', '+adp.[FirstName] AS [Name]
			FROM dbo.[GetDescendants](@admPersonId, @isRecursive,1,@HierarchyTypeId) des       
				INNER JOIN AdmPerson adp ON adp.[Id]=des.[ChildId]      
				LEFT JOIN FWKApplicationUser fac ON adp.[fwkDomainUserId]=fac.[FwkDomainUserId]            
				LEFT JOIN FwkDomainUser fdu ON adp.[fwkDomainUserId]=fdu.[Id]      
			WHERE      
				((fac.[ModuleRoleBitmask] & 512)>0) or ((fac.[ModuleRoleBitmask] & 1024)>0)      
				AND               
				ISNULL(adp.[firstName], '*') LIKE @firstName              
				AND              
				ISNULL(adp.[lastName], '*') LIKE @lastName              
				AND              
				ISNULL(fdu.[userName], '*') LIKE @userName    
				Order by adp.[LastName]      
		END      
	ELSE IF (@isRecursive=0)      
	BEGIN      
		SELECT des.[ChildId],adp.[LastName]+ ', '+adp.[FirstName] AS [Name] 
		FROM dbo.[GetDescendants](@admPersonId, @isRecursive,1,@HierarchyTypeId) des       
			INNER JOIN AdmPerson adp ON adp.[Id]=des.[ChildId]      
			LEFT JOIN FWKApplicationUser fac ON adp.[fwkDomainUserId]=fac.[FwkDomainUserId]      
			LEFT JOIN FwkDomainUser fdu ON adp.[fwkDomainUserId]=fdu.[Id]      
		WHERE       
			((fac.[ModuleRoleBitmask] & 512)>0)       
			AND              
			ISNULL(adp.[firstName], '*') LIKE @firstName              
			AND              
			ISNULL(adp.[lastName], '*') LIKE @lastName              
			AND              
			ISNULL(fdu.[userName], '*') LIKE @userName     
		ORDER BY adp.[LastName]     
	END      
  END        
END    


